from datetime import datetime
from sqlalchemy import func, select, exc, and_, or_
from app.config.db import DBUtils
from app.model.BusinessInfoModel import BusinessInfo
from app.model.CompanyPositionModel import CompanyPositionModel
from app.model.actualcontrollerModel import ActualController
from app.model.RiskTitlelModel import RiskTitle

class wareHouseService:
    def __init__(self):
        self.db_utils = DBUtils()
        # 缓存结构：{method_name: {"last_pagesize": int, "cached_total_pages": int}}
        self._pagination_cache = {}

    def _get_cache_key(self, method_name: str) -> str:
        """生成唯一缓存键"""
        return f"{method_name}_cache"

    def _paginated_query(self, session, model, page, pagesize, cache_key, company, year, risktype, order_by=None):
        """通用分页查询逻辑"""
        offset = (int(page) - 1) * int(pagesize)

        # 初始化方法缓存（如果不存在）
        if cache_key not in self._pagination_cache:
            self._pagination_cache[cache_key] = {
                "last_pagesize": None,
                "cached_total_pages": None
            }
        cache = self._pagination_cache[cache_key]

        try:
            # 判断是否需要重新计算总页数
            if page == 1 or pagesize != cache["last_pagesize"]:
                if model == RiskTitle:
                    total = session.query(func.count(model.ID)).filter(
                        and_(
                            model.GSMC.like(f"%{company}%"),
                            model.YEAR.like(f"%{year}%"),
                            model.FXLX.like(f"%{risktype}%")
                        )).scalar()
                else:
                    total = session.query(func.count(model.ID)).filter(model.GSMC.like(f"%{company}%")).scalar()
                # total_pages = (int(total) + int(pagesize) - 1) // int(pagesize)
                total_pages = int(total)
                # 更新缓存
                cache["last_pagesize"] = pagesize
                cache["cached_total_pages"] = total_pages
            else:
                total_pages = cache["cached_total_pages"]

            # 构建基础查询
            if model == RiskTitle:
                query = session.query(model).filter(
                        and_(
                            model.GSMC.like(f"%{company}%"),
                            model.YEAR.like(f"%{year}%"),
                            model.FXLX.like(f"%{risktype}%")
                        ))
            else:
                query = session.query(model).filter(model.GSMC.like(f"%{company}%") )
            # print(str(query.statement.compile(compile_kwargs={"literal_binds": True})))
            # 添加排序（如果指定）
            if order_by is not None:
                query = query.order_by(order_by)
            # 执行分页查询
            items = query.offset(offset).limit(pagesize).all()

            # 转换数据格式
            data = [item.to_dict() for item in items] if items else []

            result = {
                'info': data,
                'total': total_pages
            }
            # 返回数据及总页数（仅在需要时）
            return result

        except exc.SQLAlchemyError as e:
            return [{'error': str(e)}]
        finally:
            try:
                self.db_utils.remove_session()
            except Exception as remove_error:
                print(f"Failed to remove session: {remove_error}")

    # -------------------- 具体业务 -------------------- #
    def select_company_info(self, session, pagenum, pagesize, company):
        """查询工商信息（按时间倒序）"""
        cache_key = self._get_cache_key("select_company_info")
        result = self._paginated_query(
            session=session,
            model=BusinessInfo,
            page=pagenum,
            pagesize=pagesize,
            cache_key=cache_key + company,
            company=company,
            year='',
            risktype='',
            order_by=BusinessInfo.CJRQ.desc()  # 按时间倒序
        )
        return result

    def select_user_position(self, session, pagenum, pagesize, company):
        """查询高管股东信息"""
        cache_key = self._get_cache_key("select_user_position")
        result = self._paginated_query(
            session=session,
            model=CompanyPositionModel,
            page=pagenum,
            pagesize=pagesize,
            cache_key=cache_key + company,
            company=company,
            year='',
            risktype='',
            order_by=CompanyPositionModel.ID.desc()
        )
        return result

    def select_controller(self, session, pagenum, pagesize, company):
        """查询实际控制人信息"""
        cache_key = self._get_cache_key("select_controller")
        result = self._paginated_query(
            session=session,
            model=ActualController,
            page=pagenum,
            pagesize=pagesize,
            cache_key=cache_key + company,
            company=company,
            year='',
            risktype='',
            order_by=ActualController.ID.desc()
        )
        return result

    def select_risk_title(self, session, pagenum, pagesize, company, year, risktype):
        """查询风险标签信息"""
        cache_key = self._get_cache_key("select_risk_title")
        result = self._paginated_query(
            session=session,
            model=RiskTitle,
            page=pagenum,
            pagesize=pagesize,
            cache_key=cache_key + company + risktype + year,
            company=company,
            year = year,
            risktype= risktype,
            order_by=RiskTitle.ID.desc()
        )
        return result

# class wareHouseGsxxService:
#     def __init__(self):
#         self.db_utils = DBUtils()
#         # 缓存变量：记录上一次的 pagesize 和对应的总页数
#         self._last_pagesize = None
#         self._cached_total_pages = None
#
#     # 查看工商信息
#     def select_company_info(self, session, pagenum, pagesize):
#         # 计算偏移量（offset）
#         offset = (pagenum - 1) * pagesize
#         try:
#             result = session.query(BusinessInfo).offset(offset).limit(pagesize).all() \
#                     .order_by(BusinessInfo.CJRQ.desc()).first()
#             if result:
#                 return [result.to_dict()]
#             else:
#                 return []
#         except exc.SQLAlchemyError as e:
#             error_response = [{'error': str(e)}]
#             return error_response
#         finally:
#             try:
#                 self.db_utils.remove_session()
#             except Exception as remove_error:
#                 print(f"Failed to rollback transaction: {remove_error}")
#
#
#
#     # 查询高管股东信息
#     def select_user_position(self, session, pagenum, pagesize):
#         # 计算偏移量（offset）
#         offset = (pagenum - 1) * pagesize
#         # 查询全部
#         try:
#             position_list = session.query(CompanyPositionModel).offset(offset).limit(pagesize).all()
#             if position_list:
#                 return [item.to_all_dict() for item in position_list]
#             else:
#                 return []
#         except exc.SQLAlchemyError as e:
#             error_response = [{'error': str(e)}]
#             return error_response
#         finally:
#             try:
#                 self.db_utils.remove_session()
#             except Exception as remove_error:
#                 print(f"Failed to rollback transaction: {remove_error}")
#
#         # 查询实际控制人信息
#     def select_controller(self, session, pagenum, pagesize):
#         # 计算偏移量（offset）
#         offset = (pagenum - 1) * pagesize
#         # 查询全部
#         try:
#             position_list = session.query(ActualController).offset(offset).limit(pagesize).all()
#             if position_list:
#                 return [item.to_all_dict() for item in position_list]
#             else:
#                 return []
#         except exc.SQLAlchemyError as e:
#             error_response = [{'error': str(e)}]
#             return error_response
#         finally:
#             try:
#                 self.db_utils.remove_session()
#             except Exception as remove_error:
#                 print(f"Failed to rollback transaction: {remove_error}")

    # def select_user_position(self, session, pagenum, pagesize):
    #     offset = (pagenum - 1) * pagesize
    #     try:
    #         # 判断是否需要重新计算总页数
    #         if pagenum == 1 or pagesize != self._last_pagesize:
    #             total = session.query(func.count(CompanyPositionModel.id)).scalar()
    #             total_pages = (total + pagesize - 1) // pagesize
    #             # 更新缓存
    #             self._last_pagesize = pagesize
    #             self._cached_total_pages = total_pages
    #         else:
    #             total_pages = self._cached_total_pages  # 使用缓存值
    #
    #         # 执行分页查询
    #         position_list = session.query(CompanyPositionModel).offset(offset).limit(pagesize).all()
    #         data = [item.to_all_dict() for item in position_list] if position_list else []
    #         result = {
    #             'data': data,
    #             'total': total_pages
    #         }
    #         return result
    #
    #     except exc.SQLAlchemyError as e:
    #         error_response = [{'error': str(e)}]
    #         return error_response
    #     finally:
    #         try:
    #             self.db_utils.remove_session()
    #         except Exception as remove_error:
    #             print(f"Failed to remove session: {remove_error}")